<?php

namespace MLM\Model\Product;


use MLM\Model\Product;

class Storage extends \MLM\Model\Generic\Db\Storage {
    
    public function save(Product $product){

        $insert = $this->_sql->insert()
                ->into('product')
                ->values($product->pull());
        
        $result = $this->execute($insert);
        
        return $result;
    }

    public function edit($dataProduct, $where){

        $update = $this->_sql->update()
                ->table('product')
                ->set($dataProduct)
                ->where($where);

        $result = $this->execute($update);

        return $result;

    }


    public function delProduct($prodId){

        $stmt = $this->_db->query("delete from product_stock where product_id = $prodId");
        $stmt->execute() ;

        $stmt2 = $this->_db->query("delete from product_price where product_id = $prodId");
        $stmt2->execute() ;


        $stmt3 = $this->_db->query("delete from product where product_id = $prodId");

        return $stmt3->execute() ;

    }

    public function listProduct(){

        $stmt = $this->_db->query("SELECT * from product order by product_code");
        return $stmt->execute() ;
    }

    public function listProductOwner(){

        $stmt = $this->_db->query("SELECT * from product_owner order by product_owner_name");
        return $stmt->execute() ;
    }

    public function listSuplier(){
        
        $stmt = $this->_db->query("SELECT * from supliers order by suplier_name");
        return $stmt->execute() ;
    }
    
    public function getProductList($prodCode, $suplier, $date1, $date2, $start, $limit, $searchField = "") {

        /*$sql = "select count(1) as total
                from product p, product_price pp
                where pp.product_id = p.product_id ";
         * 
         */

        $sql = "select count(1) as total from product ";

        if ($prodCode != ""){            
            $sql .= " where product_code = '$prodCode' ";
        }


        if ($suplier != ""){

            if ($prodCode != "") $sql .= " and ";
            else $sql .= " where ";

            $sql .= " suplier_id = $suplier ";
        }

/*
        if ($date1 != ""){
            $sql .= " and STR_TO_DATE(DATE_FORMAT(start_dat, '%Y%m%d'),'%Y%m%d') > STR_TO_DATE('$date1', '%Y%m%d') ";
        }

        if ($date2 != ""){
            $sql .= " and STR_TO_DATE(DATE_FORMAT(end_dat, '%Y%m%d'),'%Y%m%d') < STR_TO_DATE('$date2', '%Y%m%d') ";
        }
 * 
 */

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }


        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        /*$sql = "select *, getParentProductName(p.parent_product_id) product_parent
                from product p, product_price pp, product_stock ps, supliers s, product_owner po
                where pp.product_id = p.product_id and ps.product_id = p.product_id
                and s.suplier_id = p.suplier_id and po.product_owner_id = p.product_owner_id ";
         * 
         */


        $sql = "select *, getParentProductName(p.parent_product_id) product_parent
                from product p, supliers s, product_owner po
                where s.suplier_id = p.suplier_id and po.product_owner_id = p.product_owner_id ";


        if ($prodCode != ""){
            $sql .= " and product_code = '$prodCode' ";
        }

        if ($suplier != ""){
            $sql .= " and p.suplier_id = $suplier ";
        }

        /*
        if ($date1 != ""){
            $sql .= " and STR_TO_DATE(DATE_FORMAT(start_dat, '%Y%m%d'),'%Y%m%d') > STR_TO_DATE('$date1', '%Y%m%d') ";
        }

        if ($date2 != ""){
            $sql .= " and STR_TO_DATE(DATE_FORMAT(end_dat, '%Y%m%d'),'%Y%m%d') < STR_TO_DATE('$date2', '%Y%m%d') ";
        }
         * 
         */

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by product_code ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                $aksi = "<a href='javascript: void(0)' onclick='price(".$res['product_id'].")'>Harga</a>";
                $aksi .= " | <a href='javascript: void(0)' onclick='stock(".$res['product_id'].")'>Stok</a>";
                $aksi .= " | <a href='javascript: void(0)' onclick='edit(".$res['product_id'].")'>Edit</a>";
                $aksi .= " | <a href='javascript: void(0)' onclick='del(".$res['product_id'].")'>Hapus</a>";

                array_push($dataBonus, array("KodeProduk" => $res['product_code'],
                                             "NamaProduk" => $res['product_name'],
                                             "DeskProduk" => $res['product_desc'],
                                             "ParentProd" => $res['product_parent'],
                                             "Owner" => $res['product_owner_name'],
                                             "Suplier" => $res['suplier_name'],
                                             "HargaAsal" => $res['basic_price'],
                                             "HargaJual" => $res['sale_price'],
                                             "TglHarga" => $res['start_dat'].' - '.$res['end_dat'],
                                             "Jumlah" => $res['qty'],
                                             "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }
	

	
    public function getProductDetail($key){

        /*$sql = "select *
                from product p, product_price pp, product_stock ps, supliers s, product_owner po
                where p.product_id = ? and pp.product_id = p.product_id and ps.product_id = p.product_id
                and s.suplier_id = p.suplier_id and po.product_owner_id = p.product_owner_id ";
         *
         */

        $sql = "select *
                from product p, supliers s, product_owner po
                where p.product_id = ? 
                and s.suplier_id = p.suplier_id and po.product_owner_id = p.product_owner_id ";


        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));
        return $res;
    }

    public function getProductParent($key){

        $sql = "select *
                from product where product_id = ? ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));
        return $res;
    }

    

    public function updateProduct($data, $where){

        $sql = $this->update()
                    ->table('members')
                    ->set($data)
                    ->where($where);

        $result = $this->execute($sql);

        return $result;
    }


    public function getProducts($searchBy, $op, $val){

        $sql = "select *
                from product ";

        if ($searchBy != "" && $val != ""){
            $sql .= " where $searchBy $op $val ";
        }

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute();
        return $res;

    }

    
}
